--    Program: 		CAPPOIMP_APPROVED_SUPPLIER_LIST_ATTRIBUTE.sql
--    Description:  Import PO APPROVED SUPPLIER LIST ATTRIBUTE information 
--    Revision:     1st     
--    Parameter: 	
--    Created By:   Kenny Leung
--    Date:         27-Jan-2006

set doc off;      
set verify off;
set serveroutput on size 1000000;
whenever sqlerror exit failure rollback;

declare   
   l_processed_count		number;
   l_rejected_count 		number;
   l_err_msg				varchar2(2000);
   l_err_msg_tmp			varchar2(2000);
   
   l_asl_id					number;
                    		                 		
   cursor cur_po_asl_attributes is
      select * from CRST_PO_ASL_ATTRIBUTES;

begin
   l_processed_count := 0;
   l_rejected_count  := 0;
   l_err_msg := '';
   
   for cur in cur_po_asl_attributes loop   	
     l_err_msg_tmp := '';
     
     l_asl_id := null; 	
     begin
       SELECT asl_id_n
       INTO   l_asl_id
       FROM   CRST_PO_APPROVED_SUPPLIER_LIST
       WHERE  asl_id = cur.ASL_ID;
     exception
       when others then
         l_err_msg_tmp := 'New ASL ID '||cur.ASL_ID||' not found';
     end;
     
     begin
       -- import data
       insert into PO_ASL_ATTRIBUTES
       (
          ASL_ID                          ,
          USING_ORGANIZATION_ID           ,
          LAST_UPDATE_DATE                ,
          LAST_UPDATED_BY                 ,
          CREATION_DATE                   ,
          CREATED_BY                      ,
          DOCUMENT_SOURCING_METHOD        ,
          RELEASE_GENERATION_METHOD       ,
          PURCHASING_UNIT_OF_MEASURE      ,
          ENABLE_PLAN_SCHEDULE_FLAG       ,
          ENABLE_SHIP_SCHEDULE_FLAG       ,
          PLAN_SCHEDULE_TYPE              ,
          SHIP_SCHEDULE_TYPE              ,
          PLAN_BUCKET_PATTERN_ID          ,
          SHIP_BUCKET_PATTERN_ID          ,
          ENABLE_AUTOSCHEDULE_FLAG        ,
          SCHEDULER_ID                    ,
          ENABLE_AUTHORIZATIONS_FLAG      ,
          VENDOR_ID                       ,
          VENDOR_SITE_ID                  ,
          ITEM_ID                         ,
          CATEGORY_ID                     ,
          ATTRIBUTE_CATEGORY              ,
          ATTRIBUTE1                      ,
          ATTRIBUTE2                      ,
          ATTRIBUTE3                      ,
          ATTRIBUTE4                      ,
          ATTRIBUTE5                      ,
          ATTRIBUTE6                      ,
          ATTRIBUTE7                      ,
          ATTRIBUTE8                      ,
          ATTRIBUTE9                      ,
          ATTRIBUTE10                     ,
          ATTRIBUTE11                     ,
          ATTRIBUTE12                     ,
          ATTRIBUTE13                     ,
          ATTRIBUTE14                     ,
          ATTRIBUTE15                     ,
          LAST_UPDATE_LOGIN               ,
          REQUEST_ID                      ,
          PROGRAM_APPLICATION_ID          ,
          PROGRAM_ID                      ,
          PROGRAM_UPDATE_DATE             ,
          PRICE_UPDATE_TOLERANCE          ,
          PROCESSING_LEAD_TIME            ,
          MIN_ORDER_QTY                   ,
          FIXED_LOT_MULTIPLE              ,
          DELIVERY_CALENDAR               ,
          COUNTRY_OF_ORIGIN_CODE          ,
          ENABLE_VMI_FLAG                 ,
          VMI_MIN_QTY                     ,
          VMI_MAX_QTY                     ,
          ENABLE_VMI_AUTO_REPLENISH_FLAG  ,
          VMI_REPLENISHMENT_APPROVAL      ,
          CONSIGNED_FROM_SUPPLIER_FLAG    ,
          LAST_BILLING_DATE               ,
          CONSIGNED_BILLING_CYCLE          
       
       )
       values
       (
            l_asl_id                            ,
            CAPBRIMP_MAPPING.GetOrganizationMapping(cur.USING_ORGANIZATION_ID),
            cur.LAST_UPDATE_DATE                ,
            CUR.LAST_UPDATED_BY,
            cur.CREATION_DATE                   ,
            CAPBRIMP_MAPPING.GetUserMapping(cur.CREATED_BY),
            cur.DOCUMENT_SOURCING_METHOD        ,
            cur.RELEASE_GENERATION_METHOD       ,
            cur.PURCHASING_UNIT_OF_MEASURE      ,
            cur.ENABLE_PLAN_SCHEDULE_FLAG       ,
            cur.ENABLE_SHIP_SCHEDULE_FLAG       ,
            cur.PLAN_SCHEDULE_TYPE              ,
            cur.SHIP_SCHEDULE_TYPE              ,
            cur.PLAN_BUCKET_PATTERN_ID          ,
            cur.SHIP_BUCKET_PATTERN_ID          ,
            cur.ENABLE_AUTOSCHEDULE_FLAG        ,
            cur.SCHEDULER_ID                    ,
            cur.ENABLE_AUTHORIZATIONS_FLAG      ,
            CAPPOIMP_MAPPING.GetVendorIDMapping(cur.VENDOR_ID),
            CAPPOIMP_MAPPING.GetVendorSiteIDMapping(cur.VENDOR_SITE_ID),
            --CAPPOIMP_MAPPING.GetItemIDMapping(cur.USING_ORGANIZATION_ID, cur.ITEM_ID),
            CAPPOIMP_MAPPING.GetItemIDMapping(cur.ITEM_ID),
            cur.CATEGORY_ID                     ,
            cur.ATTRIBUTE_CATEGORY              ,
            cur.ATTRIBUTE1                      ,
            cur.ATTRIBUTE2                      ,
            cur.ATTRIBUTE3                      ,
            cur.ATTRIBUTE4                      ,
            cur.ATTRIBUTE5                      ,
            cur.ATTRIBUTE6                      ,
            cur.ATTRIBUTE7                      ,
            cur.ATTRIBUTE8                      ,
            cur.ATTRIBUTE9                      ,
            cur.ATTRIBUTE10                     ,
            cur.ATTRIBUTE11                     ,
            cur.ATTRIBUTE12                     ,
            cur.ATTRIBUTE13                     ,
            cur.ATTRIBUTE14                     ,
            cur.ATTRIBUTE15                     ,
            cur.LAST_UPDATE_LOGIN               ,
            cur.REQUEST_ID                      ,
            cur.PROGRAM_APPLICATION_ID          ,
            cur.PROGRAM_ID                      ,
            cur.PROGRAM_UPDATE_DATE             ,
            cur.PRICE_UPDATE_TOLERANCE          ,
            cur.PROCESSING_LEAD_TIME            ,
            cur.MIN_ORDER_QTY                   ,
            cur.FIXED_LOT_MULTIPLE              ,
            cur.DELIVERY_CALENDAR               ,
            cur.COUNTRY_OF_ORIGIN_CODE          ,
            cur.ENABLE_VMI_FLAG                 ,
            cur.VMI_MIN_QTY                     ,
            cur.VMI_MAX_QTY                     ,
            cur.ENABLE_VMI_AUTO_REPLENISH_FLAG  ,
            cur.VMI_REPLENISHMENT_APPROVAL      ,
            cur.CONSIGNED_FROM_SUPPLIER_FLAG    ,
            cur.LAST_BILLING_DATE               ,
            cur.CONSIGNED_BILLING_CYCLE         
       );  
     exception
       when others then
     	 l_err_msg_tmp := sqlerrm;
     end;	  	    
     
     if l_err_msg_tmp IS NULL then
       l_processed_count := l_processed_count + 1;  
     else
       l_rejected_count := l_rejected_count + 1;
       l_err_msg := l_err_msg_tmp; 
     end if;
   end loop;

   if l_rejected_count = 0 then
   	 commit;
   else
     rollback;
   end if;
   
   -- insert into log table
   insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
    values ('CAPPOIMP_APPROVED_SUPPLIER_LIST_ATTRIBUTE', sysdate, l_processed_count, l_rejected_count, l_processed_count-l_rejected_count, l_err_msg );
   
   commit;
exception 
  when others then
    dbms_output.put_line(sqlerrm);
    rollback;  
end;
/


--exit
